{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2023-12-24T03:59:35.632654100Z",
     "start_time": "2023-12-24T03:59:34.201674300Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "aisles = pd.read_csv(\"D:\\\\data\\\\market analysis\\\\instacart-market-basket-analysis\\\\aisles.csv\")\n",
    "orderProducts = pd.read_csv(\"D:\\\\data\\\\market analysis\\\\instacart-market-basket-analysis\\\\order_products__prior.csv\")\n",
    "orders = pd.read_csv(\"D:\\\\data\\\\market analysis\\\\instacart-market-basket-analysis\\\\orders.csv\")\n",
    "products = pd.read_csv(\"D:\\\\data\\\\market analysis\\\\instacart-market-basket-analysis\\\\products.csv\")\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:10:05.394741300Z",
     "start_time": "2023-12-24T04:09:54.942158200Z"
    }
   },
   "id": "f995c799a3c7fb7d"
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [],
   "source": [
    "from sklearn.decomposition import PCA\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn.metrics import silhouette_score"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:11:25.642349600Z",
     "start_time": "2023-12-24T04:11:21.193763800Z"
    }
   },
   "id": "f22536240bb20cc7"
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "     aisle_id                       aisle\n0           1       prepared soups salads\n1           2           specialty cheeses\n2           3         energy granola bars\n3           4               instant foods\n4           5  marinades meat preparation\n..        ...                         ...\n129       130    hot cereal pancake mixes\n130       131                   dry pasta\n131       132                      beauty\n132       133  muscles joints pain relief\n133       134  specialty wines champagnes\n\n[134 rows x 2 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>aisle_id</th>\n      <th>aisle</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>prepared soups salads</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>specialty cheeses</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>energy granola bars</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>instant foods</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>marinades meat preparation</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>129</th>\n      <td>130</td>\n      <td>hot cereal pancake mixes</td>\n    </tr>\n    <tr>\n      <th>130</th>\n      <td>131</td>\n      <td>dry pasta</td>\n    </tr>\n    <tr>\n      <th>131</th>\n      <td>132</td>\n      <td>beauty</td>\n    </tr>\n    <tr>\n      <th>132</th>\n      <td>133</td>\n      <td>muscles joints pain relief</td>\n    </tr>\n    <tr>\n      <th>133</th>\n      <td>134</td>\n      <td>specialty wines champagnes</td>\n    </tr>\n  </tbody>\n</table>\n<p>134 rows × 2 columns</p>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aisles"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:11:40.117274200Z",
     "start_time": "2023-12-24T04:11:39.962391800Z"
    }
   },
   "id": "d1b996dd6a22ad75"
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "          order_id  product_id  add_to_cart_order  reordered\n0                2       33120                  1          1\n1                2       28985                  2          1\n2                2        9327                  3          0\n3                2       45918                  4          1\n4                2       30035                  5          0\n...            ...         ...                ...        ...\n32434484   3421083       39678                  6          1\n32434485   3421083       11352                  7          0\n32434486   3421083        4600                  8          0\n32434487   3421083       24852                  9          1\n32434488   3421083        5020                 10          1\n\n[32434489 rows x 4 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>order_id</th>\n      <th>product_id</th>\n      <th>add_to_cart_order</th>\n      <th>reordered</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2</td>\n      <td>33120</td>\n      <td>1</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>28985</td>\n      <td>2</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>9327</td>\n      <td>3</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>45918</td>\n      <td>4</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2</td>\n      <td>30035</td>\n      <td>5</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>32434484</th>\n      <td>3421083</td>\n      <td>39678</td>\n      <td>6</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>32434485</th>\n      <td>3421083</td>\n      <td>11352</td>\n      <td>7</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>32434486</th>\n      <td>3421083</td>\n      <td>4600</td>\n      <td>8</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>32434487</th>\n      <td>3421083</td>\n      <td>24852</td>\n      <td>9</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>32434488</th>\n      <td>3421083</td>\n      <td>5020</td>\n      <td>10</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n<p>32434489 rows × 4 columns</p>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orderProducts"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:11:46.367938700Z",
     "start_time": "2023-12-24T04:11:46.351490900Z"
    }
   },
   "id": "1efaeec0a53a3588"
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "         order_id  user_id eval_set  order_number  order_dow  \\\n0         2539329        1    prior             1          2   \n1         2398795        1    prior             2          3   \n2          473747        1    prior             3          3   \n3         2254736        1    prior             4          4   \n4          431534        1    prior             5          4   \n...           ...      ...      ...           ...        ...   \n3421078   2266710   206209    prior            10          5   \n3421079   1854736   206209    prior            11          4   \n3421080    626363   206209    prior            12          1   \n3421081   2977660   206209    prior            13          1   \n3421082    272231   206209    train            14          6   \n\n         order_hour_of_day  days_since_prior_order  \n0                        8                     NaN  \n1                        7                    15.0  \n2                       12                    21.0  \n3                        7                    29.0  \n4                       15                    28.0  \n...                    ...                     ...  \n3421078                 18                    29.0  \n3421079                 10                    30.0  \n3421080                 12                    18.0  \n3421081                 12                     7.0  \n3421082                 14                    30.0  \n\n[3421083 rows x 7 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>order_id</th>\n      <th>user_id</th>\n      <th>eval_set</th>\n      <th>order_number</th>\n      <th>order_dow</th>\n      <th>order_hour_of_day</th>\n      <th>days_since_prior_order</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2539329</td>\n      <td>1</td>\n      <td>prior</td>\n      <td>1</td>\n      <td>2</td>\n      <td>8</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2398795</td>\n      <td>1</td>\n      <td>prior</td>\n      <td>2</td>\n      <td>3</td>\n      <td>7</td>\n      <td>15.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>473747</td>\n      <td>1</td>\n      <td>prior</td>\n      <td>3</td>\n      <td>3</td>\n      <td>12</td>\n      <td>21.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2254736</td>\n      <td>1</td>\n      <td>prior</td>\n      <td>4</td>\n      <td>4</td>\n      <td>7</td>\n      <td>29.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>431534</td>\n      <td>1</td>\n      <td>prior</td>\n      <td>5</td>\n      <td>4</td>\n      <td>15</td>\n      <td>28.0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>3421078</th>\n      <td>2266710</td>\n      <td>206209</td>\n      <td>prior</td>\n      <td>10</td>\n      <td>5</td>\n      <td>18</td>\n      <td>29.0</td>\n    </tr>\n    <tr>\n      <th>3421079</th>\n      <td>1854736</td>\n      <td>206209</td>\n      <td>prior</td>\n      <td>11</td>\n      <td>4</td>\n      <td>10</td>\n      <td>30.0</td>\n    </tr>\n    <tr>\n      <th>3421080</th>\n      <td>626363</td>\n      <td>206209</td>\n      <td>prior</td>\n      <td>12</td>\n      <td>1</td>\n      <td>12</td>\n      <td>18.0</td>\n    </tr>\n    <tr>\n      <th>3421081</th>\n      <td>2977660</td>\n      <td>206209</td>\n      <td>prior</td>\n      <td>13</td>\n      <td>1</td>\n      <td>12</td>\n      <td>7.0</td>\n    </tr>\n    <tr>\n      <th>3421082</th>\n      <td>272231</td>\n      <td>206209</td>\n      <td>train</td>\n      <td>14</td>\n      <td>6</td>\n      <td>14</td>\n      <td>30.0</td>\n    </tr>\n  </tbody>\n</table>\n<p>3421083 rows × 7 columns</p>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:11:54.437531Z",
     "start_time": "2023-12-24T04:11:54.398932800Z"
    }
   },
   "id": "8c3bd4ac47c9f744"
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "       product_id                                       product_name  \\\n0               1                         Chocolate Sandwich Cookies   \n1               2                                   All-Seasons Salt   \n2               3               Robust Golden Unsweetened Oolong Tea   \n3               4  Smart Ones Classic Favorites Mini Rigatoni Wit...   \n4               5                          Green Chile Anytime Sauce   \n...           ...                                                ...   \n49683       49684          Vodka, Triple Distilled, Twist of Vanilla   \n49684       49685                 En Croute Roast Hazelnut Cranberry   \n49685       49686                                   Artisan Baguette   \n49686       49687         Smartblend Healthy Metabolism Dry Cat Food   \n49687       49688                             Fresh Foaming Cleanser   \n\n       aisle_id  department_id  \n0            61             19  \n1           104             13  \n2            94              7  \n3            38              1  \n4             5             13  \n...         ...            ...  \n49683       124              5  \n49684        42              1  \n49685       112              3  \n49686        41              8  \n49687        73             11  \n\n[49688 rows x 4 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>product_id</th>\n      <th>product_name</th>\n      <th>aisle_id</th>\n      <th>department_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>Chocolate Sandwich Cookies</td>\n      <td>61</td>\n      <td>19</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>All-Seasons Salt</td>\n      <td>104</td>\n      <td>13</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>Robust Golden Unsweetened Oolong Tea</td>\n      <td>94</td>\n      <td>7</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>Smart Ones Classic Favorites Mini Rigatoni Wit...</td>\n      <td>38</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>Green Chile Anytime Sauce</td>\n      <td>5</td>\n      <td>13</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>49683</th>\n      <td>49684</td>\n      <td>Vodka, Triple Distilled, Twist of Vanilla</td>\n      <td>124</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>49684</th>\n      <td>49685</td>\n      <td>En Croute Roast Hazelnut Cranberry</td>\n      <td>42</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>49685</th>\n      <td>49686</td>\n      <td>Artisan Baguette</td>\n      <td>112</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>49686</th>\n      <td>49687</td>\n      <td>Smartblend Healthy Metabolism Dry Cat Food</td>\n      <td>41</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>49687</th>\n      <td>49688</td>\n      <td>Fresh Foaming Cleanser</td>\n      <td>73</td>\n      <td>11</td>\n    </tr>\n  </tbody>\n</table>\n<p>49688 rows × 4 columns</p>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:11:58.620660900Z",
     "start_time": "2023-12-24T04:11:58.604647200Z"
    }
   },
   "id": "5676f5bc6d3fe4b8"
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [],
   "source": [
    "table1 = pd.merge(orderProducts,products,on=[\"product_id\",\"product_id\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:22:54.590258400Z",
     "start_time": "2023-12-24T04:22:44.795393100Z"
    }
   },
   "id": "fc892c7fa3165b25"
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [],
   "source": [
    "table2 = pd.merge(table1,orders,on=['order_id','order_id'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:24:00.636964500Z",
     "start_time": "2023-12-24T04:23:30.554468200Z"
    }
   },
   "id": "d555e4baecdd166d"
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [],
   "source": [
    "table = pd.merge(table2,aisles,on=[\"aisle_id\",\"aisle_id\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:24:29.744961300Z",
     "start_time": "2023-12-24T04:24:13.181417200Z"
    }
   },
   "id": "19dd148d303f5387"
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "(32434489, 14)"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.shape"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:24:36.722830300Z",
     "start_time": "2023-12-24T04:24:36.682319400Z"
    }
   },
   "id": "62914eaa28d9744f"
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "          order_id  product_id  add_to_cart_order  reordered  \\\n0                2       33120                  1          1   \n1               26       33120                  5          0   \n2              120       33120                 13          0   \n3              327       33120                  5          1   \n4              390       33120                 28          1   \n...            ...         ...                ...        ...   \n32434484   3243156       20731                  1          0   \n32434485    860862       30582                  1          0   \n32434486   1333472       27906                  1          0   \n32434487   2122701       26086                  1          1   \n32434488   3168475       26086                  1          1   \n\n                          product_name  aisle_id  department_id  user_id  \\\n0                   Organic Egg Whites        86             16   202279   \n1                   Organic Egg Whites        86             16   153404   \n2                   Organic Egg Whites        86             16    23750   \n3                   Organic Egg Whites        86             16    58707   \n4                   Organic Egg Whites        86             16   166654   \n...                                ...       ...            ...      ...   \n32434484               Straight Sherry       134              5   166400   \n32434485             Natural Champagne       134              5   104017   \n32434486            Imperial Champagne       134              5    62079   \n32434487  La Grand Dame Brut Champagne       134              5    77799   \n32434488  La Grand Dame Brut Champagne       134              5     5223   \n\n         eval_set  order_number  order_dow  order_hour_of_day  \\\n0           prior             3          5                  9   \n1           prior             2          0                 16   \n2           prior            11          6                  8   \n3           prior            21          6                  9   \n4           prior            48          0                 12   \n...           ...           ...        ...                ...   \n32434484    prior             3          1                 13   \n32434485    prior            13          5                 14   \n32434486    prior            10          3                 10   \n32434487    prior             2          3                 14   \n32434488    prior             7          3                 13   \n\n          days_since_prior_order                       aisle  \n0                            8.0                        eggs  \n1                            7.0                        eggs  \n2                           10.0                        eggs  \n3                            8.0                        eggs  \n4                            9.0                        eggs  \n...                          ...                         ...  \n32434484                    12.0  specialty wines champagnes  \n32434485                    18.0  specialty wines champagnes  \n32434486                    10.0  specialty wines champagnes  \n32434487                     3.0  specialty wines champagnes  \n32434488                     2.0  specialty wines champagnes  \n\n[32434489 rows x 14 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>order_id</th>\n      <th>product_id</th>\n      <th>add_to_cart_order</th>\n      <th>reordered</th>\n      <th>product_name</th>\n      <th>aisle_id</th>\n      <th>department_id</th>\n      <th>user_id</th>\n      <th>eval_set</th>\n      <th>order_number</th>\n      <th>order_dow</th>\n      <th>order_hour_of_day</th>\n      <th>days_since_prior_order</th>\n      <th>aisle</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2</td>\n      <td>33120</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>202279</td>\n      <td>prior</td>\n      <td>3</td>\n      <td>5</td>\n      <td>9</td>\n      <td>8.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>26</td>\n      <td>33120</td>\n      <td>5</td>\n      <td>0</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>153404</td>\n      <td>prior</td>\n      <td>2</td>\n      <td>0</td>\n      <td>16</td>\n      <td>7.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>120</td>\n      <td>33120</td>\n      <td>13</td>\n      <td>0</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>23750</td>\n      <td>prior</td>\n      <td>11</td>\n      <td>6</td>\n      <td>8</td>\n      <td>10.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>327</td>\n      <td>33120</td>\n      <td>5</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>58707</td>\n      <td>prior</td>\n      <td>21</td>\n      <td>6</td>\n      <td>9</td>\n      <td>8.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>390</td>\n      <td>33120</td>\n      <td>28</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>166654</td>\n      <td>prior</td>\n      <td>48</td>\n      <td>0</td>\n      <td>12</td>\n      <td>9.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>32434484</th>\n      <td>3243156</td>\n      <td>20731</td>\n      <td>1</td>\n      <td>0</td>\n      <td>Straight Sherry</td>\n      <td>134</td>\n      <td>5</td>\n      <td>166400</td>\n      <td>prior</td>\n      <td>3</td>\n      <td>1</td>\n      <td>13</td>\n      <td>12.0</td>\n      <td>specialty wines champagnes</td>\n    </tr>\n    <tr>\n      <th>32434485</th>\n      <td>860862</td>\n      <td>30582</td>\n      <td>1</td>\n      <td>0</td>\n      <td>Natural Champagne</td>\n      <td>134</td>\n      <td>5</td>\n      <td>104017</td>\n      <td>prior</td>\n      <td>13</td>\n      <td>5</td>\n      <td>14</td>\n      <td>18.0</td>\n      <td>specialty wines champagnes</td>\n    </tr>\n    <tr>\n      <th>32434486</th>\n      <td>1333472</td>\n      <td>27906</td>\n      <td>1</td>\n      <td>0</td>\n      <td>Imperial Champagne</td>\n      <td>134</td>\n      <td>5</td>\n      <td>62079</td>\n      <td>prior</td>\n      <td>10</td>\n      <td>3</td>\n      <td>10</td>\n      <td>10.0</td>\n      <td>specialty wines champagnes</td>\n    </tr>\n    <tr>\n      <th>32434487</th>\n      <td>2122701</td>\n      <td>26086</td>\n      <td>1</td>\n      <td>1</td>\n      <td>La Grand Dame Brut Champagne</td>\n      <td>134</td>\n      <td>5</td>\n      <td>77799</td>\n      <td>prior</td>\n      <td>2</td>\n      <td>3</td>\n      <td>14</td>\n      <td>3.0</td>\n      <td>specialty wines champagnes</td>\n    </tr>\n    <tr>\n      <th>32434488</th>\n      <td>3168475</td>\n      <td>26086</td>\n      <td>1</td>\n      <td>1</td>\n      <td>La Grand Dame Brut Champagne</td>\n      <td>134</td>\n      <td>5</td>\n      <td>5223</td>\n      <td>prior</td>\n      <td>7</td>\n      <td>3</td>\n      <td>13</td>\n      <td>2.0</td>\n      <td>specialty wines champagnes</td>\n    </tr>\n  </tbody>\n</table>\n<p>32434489 rows × 14 columns</p>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:24:47.568498600Z",
     "start_time": "2023-12-24T04:24:47.526840100Z"
    }
   },
   "id": "f6d8ab667039c47d"
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [],
   "source": [
    "# 交叉表\n",
    "data = pd.crosstab(table[\"user_id\"],table[\"aisle\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T04:27:22.802019600Z",
     "start_time": "2023-12-24T04:25:48.948745600Z"
    }
   },
   "id": "76bddf7fa584141c"
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "aisle    air fresheners candles  asian foods  baby accessories  \\\nuser_id                                                          \n1                             0            0                 0   \n2                             0            3                 0   \n3                             0            0                 0   \n4                             0            0                 0   \n5                             0            2                 0   \n...                         ...          ...               ...   \n206205                        0            0                 1   \n206206                        0            4                 0   \n206207                        0            0                 0   \n206208                        0            3                 0   \n206209                        0            1                 0   \n\naisle    baby bath body care  baby food formula  bakery desserts  \\\nuser_id                                                            \n1                          0                  0                0   \n2                          0                  0                0   \n3                          0                  0                0   \n4                          0                  0                0   \n5                          0                  0                0   \n...                      ...                ...              ...   \n206205                     0                  0                0   \n206206                     0                  0                0   \n206207                     0                  1                0   \n206208                     0                  3                0   \n206209                     0                  0                0   \n\naisle    baking ingredients  baking supplies decor  beauty  beers coolers  \\\nuser_id                                                                     \n1                         0                      0       0              0   \n2                         2                      0       0              0   \n3                         0                      0       0              0   \n4                         0                      0       0              0   \n5                         0                      0       0              0   \n...                     ...                    ...     ...            ...   \n206205                    0                      0       0              0   \n206206                    4                      1       0              0   \n206207                    0                      0       0              0   \n206208                    4                      0       0              0   \n206209                    0                      0       0              0   \n\naisle    ...  spreads  tea  tofu meat alternatives  tortillas flat bread  \\\nuser_id  ...                                                               \n1        ...        1    0                       0                     0   \n2        ...        3    1                       1                     0   \n3        ...        4    1                       0                     0   \n4        ...        0    0                       0                     1   \n5        ...        0    0                       0                     0   \n...      ...      ...  ...                     ...                   ...   \n206205   ...        0    0                       0                     0   \n206206   ...        1    0                       0                     0   \n206207   ...        3    4                       0                     2   \n206208   ...        5    0                       0                     7   \n206209   ...        0    0                       0                     0   \n\naisle    trail mix snack mix  trash bags liners  vitamins supplements  \\\nuser_id                                                                 \n1                          0                  0                     0   \n2                          0                  0                     0   \n3                          0                  0                     0   \n4                          0                  0                     0   \n5                          0                  0                     0   \n...                      ...                ...                   ...   \n206205                     0                  0                     0   \n206206                     0                  1                     0   \n206207                     1                  0                     0   \n206208                     0                  0                     0   \n206209                     0                  1                     0   \n\naisle    water seltzer sparkling water  white wines  yogurt  \nuser_id                                                      \n1                                    0            0       1  \n2                                    2            0      42  \n3                                    2            0       0  \n4                                    1            0       0  \n5                                    0            0       3  \n...                                ...          ...     ...  \n206205                               0            0       5  \n206206                               1            0       0  \n206207                              11            0      15  \n206208                               0            0      33  \n206209                               0            0       3  \n\n[206209 rows x 134 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>aisle</th>\n      <th>air fresheners candles</th>\n      <th>asian foods</th>\n      <th>baby accessories</th>\n      <th>baby bath body care</th>\n      <th>baby food formula</th>\n      <th>bakery desserts</th>\n      <th>baking ingredients</th>\n      <th>baking supplies decor</th>\n      <th>beauty</th>\n      <th>beers coolers</th>\n      <th>...</th>\n      <th>spreads</th>\n      <th>tea</th>\n      <th>tofu meat alternatives</th>\n      <th>tortillas flat bread</th>\n      <th>trail mix snack mix</th>\n      <th>trash bags liners</th>\n      <th>vitamins supplements</th>\n      <th>water seltzer sparkling water</th>\n      <th>white wines</th>\n      <th>yogurt</th>\n    </tr>\n    <tr>\n      <th>user_id</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>3</td>\n      <td>1</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>42</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>206205</th>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>206206</th>\n      <td>0</td>\n      <td>4</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>206207</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>3</td>\n      <td>4</td>\n      <td>0</td>\n      <td>2</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>11</td>\n      <td>0</td>\n      <td>15</td>\n    </tr>\n    <tr>\n      <th>206208</th>\n      <td>0</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3</td>\n      <td>0</td>\n      <td>4</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>5</td>\n      <td>0</td>\n      <td>0</td>\n      <td>7</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>33</td>\n    </tr>\n    <tr>\n      <th>206209</th>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3</td>\n    </tr>\n  </tbody>\n</table>\n<p>206209 rows × 134 columns</p>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T05:39:47.974770400Z",
     "start_time": "2023-12-24T05:39:47.905780400Z"
    }
   },
   "id": "6f7a6e67fe1d327b"
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "(206209, 134)"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T05:40:00.439679500Z",
     "start_time": "2023-12-24T05:40:00.397345800Z"
    }
   },
   "id": "7f641dc5ebce0007"
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [],
   "source": [
    "# 特征工程\n",
    "pcaTransfer = PCA(n_components=0.9)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T05:41:28.192135200Z",
     "start_time": "2023-12-24T05:41:28.158144200Z"
    }
   },
   "id": "c849cca5db2f01fb"
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [],
   "source": [
    "transferData = pcaTransfer.fit_transform(data)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T05:41:34.547590Z",
     "start_time": "2023-12-24T05:41:31.666367300Z"
    }
   },
   "id": "5a62676a665b1a10"
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "(206209, 27)"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transferData.shape"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T05:41:53.360099500Z",
     "start_time": "2023-12-24T05:41:53.328650600Z"
    }
   },
   "id": "7e0dce051db5712a"
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1416: FutureWarning: The default value of `n_init` will change from 10 to 'auto' in 1.4. Set the value of `n_init` explicitly to suppress the warning\n",
      "  super()._check_params_vs_input(X, default_n_init=10)\n"
     ]
    }
   ],
   "source": [
    "estimator = KMeans(n_clusters=5)\n",
    "yPre = estimator.fit_predict(transferData)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T05:42:39.620580500Z",
     "start_time": "2023-12-24T05:42:36.805405500Z"
    }
   },
   "id": "9d2071c2ed4b729d"
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [
    {
     "data": {
      "text/plain": "0.43271528136346304"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 模型评估\n",
    "silhouette_score(transferData,yPre)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T05:53:37.477250400Z",
     "start_time": "2023-12-24T05:43:29.728553100Z"
    }
   },
   "id": "1a6e762f54ebffdc"
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "array([0, 3, 0, ..., 3, 1, 0])"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yPre"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T07:48:14.999375100Z",
     "start_time": "2023-12-24T07:48:14.947297900Z"
    }
   },
   "id": "13b7ea7d972e00e6"
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [
    {
     "data": {
      "text/plain": "array([[-2.42156587e+01,  2.42942720e+00, -2.46636975e+00, ...,\n         6.86800336e-01,  1.69439402e+00, -2.34323022e+00],\n       [ 6.46320806e+00,  3.67511165e+01,  8.38255336e+00, ...,\n         4.12121252e+00,  2.44689740e+00, -4.28348478e+00],\n       [-7.99030162e+00,  2.40438257e+00, -1.10300641e+01, ...,\n         1.77534453e+00, -4.44194030e-01,  7.86665571e-01],\n       ...,\n       [ 8.61143331e+00,  7.70129866e+00,  7.95240226e+00, ...,\n        -2.74252456e+00,  1.07112531e+00, -6.31925661e-02],\n       [ 8.40862199e+01,  2.04187340e+01,  8.05410372e+00, ...,\n         7.27554259e-01,  3.51339470e+00, -1.79079914e+01],\n       [-1.39534562e+01,  6.64621821e+00, -5.23030367e+00, ...,\n         8.25329076e-01,  1.38230701e+00, -2.41942061e+00]])"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transferData"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-12-24T07:48:45.247740600Z",
     "start_time": "2023-12-24T07:48:45.215681300Z"
    }
   },
   "id": "ee25223a1778d6f2"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   },
   "id": "a52b8e981ce26b63"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
